<?php

/**
 * Quick and dirty MS SQL Server 2012 to CSV export tool for Codex Vlaanderen
 *
 * @author Bart Hanssens (bart.hanssens@fedict.be)
 * @version 1.0
 * @date 2013-18-02
 */
 
 
 /**
  * Clean up a database row, so it can be exported to CSV
  * Currently the only cleaning up action is to format a DateTime
  *
  * @param array $row
  *  A row
  * @retval array
  *  Array with fields
  */
function cleanup_row($row) {
	$fields = array();
	foreach ($row as $field) {
		if (is_object($field) && (get_class($field) == "DateTime")) {
			array_push($fields, $field->format('Y-m-d'));
		} else {
			array_push($fields, $field);
		}
	}
	return $fields;
}

/**
 * Get the names of the columns
 *
 * @param resource $stmt
 *  SQL statement
 */
function meta_fields($stmt) {
	$names = array();
	
	$fields = sqlsrv_field_metadata($stmt);
	if ($fields == null) {
		echo print_r(sqlsrv_errors(), true) . "\n";
	} else {
		foreach($fields as $field) {
			array_push($names, $field['Name']);
		}
	}
	return $names;
}

/**
 * Export table to CSV.
 * Files will be exported in the current working directory.
 * Existing files will be overwritten.
 *
 * @param resource $conn
 *  MS SQL Server connection
 * @param string $table
 *  table name
 */
function dump_table($conn, $table) {
	$sql = "SELECT * FROM " . $table;
	echo $sql . "\n";
	
	$stmt = sqlsrv_query($conn, $sql);
	if ($stmt == null) {
		echo print_r(sqlsrv_errors(), true) . "\n";
		return;
	}
	
	$file = fopen($table . ".csv", "w+");
	$fields = meta_fields($stmt);
	fputcsv($file, $fields, ";");

	$count = 0;
	$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);

	while (!is_null($row)) {
		$fields = cleanup_row($row);
		fputcsv($file, $fields, ";");
		$count++;
		if ($count % 1000 == 0) {
			echo $count . "\n";
		}
		$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
	} 

	fclose($file);
	echo $table . " done " . $count . " records\n";
	sqlsrv_free_stmt($stmt);
}

/**
 * Export all tables to CSV
 *
 * @param resource $conn
 *  MS SQL Server connection 
 */
function dump_all($conn) {
	$tables = array(
		"LX_Bestand",
		"LX_Deelcd",
		"LX_Hyperlink",
		"LX_Relatietype",
		"LX_Synoniemlink_Trefwoord",
		"LX_Thema",
		"LX_ThemaLink_Hyperlink",
		"LX_Trefwoord",
		"SQL_Parameter",
		"WG_Arthist_Doc_Relatielink",
		"WG_Artikel",
		"WG_Artikelhistoriek",
		"WG_Artikelhistoriek_Relatielink",
		"WG_Bestandlink_Artikelhistoriek",
		"WG_Deelcdlink",
		"WG_Document",
		"WG_Document_Arbitragelink",
		"WG_Document_Errata",
		"WG_Document_Parlvoorblink",
		"WG_Document_Relatielink",
		"WG_Hoofdstuk",
		"WG_Themalink",
		"WG_Trefwoordlink_Document",
		"WG_Type",
	);

	foreach($tables as $table) {
		dump_table($conn, $table);
	}
}

/**
 * Get a connection to the local database
 * @retval resource
 *  MS SQL Server connection
 */
function connection() {
	$server = "AFI-PDEL101209\SQLEXPRESS";
	$db = array ("Database" => "codexws");
	return sqlsrv_connect($server, $db);
}

/**
 * Main 
 */
function main() {
	$conn = connection();
	
	if ($conn == false) {
		echo "Connection failed\n";
	} else {
		echo "Connected\n";
		dump_all($conn);
		sqlsrv_close($conn);
	}
}

main();

?>